from django.shortcuts import render
from .models import Student,Score
from django.db.models import F,Q
# Create your views here.

stu_mgr = Student.objects
sco_mgr = Score.objects
#1. 查询所有学生的总分
total = sco_mgr.all().values('stu_no',total=F('math') + F('chinese') + F('english')).values('stu_no','total')
stu_text = stu_mgr.all().values('stu_no','name')
bjg = sco_mgr.filter(Q(math__lt=60) | Q(chinese__lt=60) |Q(english__lt=60)).count()
jg = sco_mgr.all().count()-bjg
math = sco_mgr.all().values('math')
print(math)
stu_count =  stu_mgr.all().count()
name_dict = {}
for dicts in stu_text:
    name_dict[dicts['stu_no']]=dicts['name']

def no_to_dict(obj):
    for i in obj:
        i['stu_no']=name_dict[i['stu_no']]
    return obj

def sort_dict(dicts):
    return int(dicts['total'])

no_to_dict(total)

def index(request):
    math = [i['math'] for i in sco_mgr.all().values('math')]
    chinese = [i['chinese'] for i in sco_mgr.all().values('chinese')]
    english = [i['english'] for i in sco_mgr.all().values('english')]
    mydict = {
        'stu_no':[i['stu_no'] for i in sorted(total,key=sort_dict,reverse=True)],
        'total': [float(i['total']) for i in sorted(total,key=sort_dict,reverse=True)],
        'tongji': [{'name':'及格人数','value':jg},{'name':'不及格人数','value':bjg}],
        'math': math,
        'chinese': chinese,
        'english': english,
    }
    return render(request,'index.html',{'mydata':mydict})
